﻿using Dapper;
using Domain.Entity.System_operation;
using Domain.Entity.System_Ordermanagement;
using Domain.IRepository.System_operation;
using Domain.IRepository.System_Order;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.System_operation
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Bill_olRepository : IBill_olRepository
    {
        public MyDbContext myDbContext { get; set; }

        public Bill_olRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public int Count()
        {
            string sql = "select * from [dbo].[Bill_ol] where Del_flag=0";
            //int conut=myDbContext.Database.GetDbConnection().Query<Orders>(sql).Count();
            int count = myDbContext.Bill_ols.Count();
            return count;
        }

        public List<Bill_ol> GetBill_ols(int currentPage, int PageSize)
        {
            string sql = "SELECT * FROM [dbo].[Bill_ol] where Del_flag=0";
            return myDbContext.Database.GetDbConnection().Query<Bill_ol>(sql).ToList();
        }

        public bool DelBill(int id)
        {
            string sql = $"update Bill_ol set Del_flag=1  where Billol_Id={id}";
            return myDbContext.Database.GetDbConnection().Execute(sql) > 0;
        } 

        public List<Bill_ol> Bill_condition(string ? Origin_place, string ? Det_place, string ? Pickup_endtime, int currentPage, int PageSize)
        {
            string sql = $"select * from [dbo].[Bill_ol] where Del_flag=0";
            if (Pickup_endtime != null && Pickup_endtime != "")
            {
                string[] time1 = Pickup_endtime.Split(',');
                sql += $" and [Pickup_endtime]>='{time1[0]}' and [Pickup_endtime]<='{time1[1]}'";
            }
            if (Origin_place != null && Origin_place != "")
            {
                sql += $" and [Origin_place] like '%{Origin_place}%'";
            }
            if (Det_place != null && Det_place != "")
            {
                sql += $" and [Det_place] like '%{Det_place}%'";
            }
            List<Bill_ol> list = myDbContext.Database.GetDbConnection().Query<Bill_ol>(sql).ToList();
            var bill = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return bill;
        }

        public List<Bill_ol> GetBill_XQ(int Billol_Id)
        {
            string sql = $@"select * from [dbo].[Bill_ol] where Billol_Id='{Billol_Id}'";
            List<Bill_ol> list = myDbContext.Database.GetDbConnection().Query<Bill_ol>(sql).ToList();
            return list;

        }

        public List<Orders> GetBill_Order(string Order_Id)
        {
            string sql = $@"select d.Cg_name,Bill_method,Weight,Freight_costs,Packag_fee,Handling_fee,Other_fee,Freight_count,
                         Cg_total=(select SUM(Cg_num) from [dbo].[Order_good]),
                         Cg_totalvolume=(select SUM(Cg_volume) from [dbo].[Cargo_ment]),
                         Cg_totalweight=(select SUM(Cg_weight) from [dbo].[Cargo_ment])
                         from [dbo].[Order_good] as a
                         inner join [dbo].[Orders] as b on a.Ord_id=b.Ord_id
                         inner join [dbo].[Bill_ol] as c on a.Ord_id=b.Ord_id
                         inner join [dbo].[Cargo_ment] as d on a.Cg_id=d.Cg_id
                         where c.Order_Id='{Order_Id}'";
            List<Orders> list = myDbContext.Database.GetDbConnection().Query<Orders>(sql).ToList();
            return list;
        }

        public int THZT(string Ord_Id)
        {
            string sql = $@"update Bill_ol set [Bill_Status]=1 where Ord_Id='{Ord_Id}'";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }
    }
}
